﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Web.Http;
using DotNetDAL.Connector;
using DotNetDAL.DbStruct;
using DotNetDAL.Entity;
using DotNetDAL.Extensions;


namespace DotNetDALtest.Controllers
{
    public class TestController : ApiController
    {
        private string connectionString
        {
            get { return System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString; }
        }

        [HttpGet]
        public dynamic add()
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);
           bool add= dal.Add("test3", new
            {
                a = DotNetDAL.Snowflake.SnowflakeIdWorker.CreateId(),
                //b8 = "13",
                c = DateTime.Now,
                d = "add"+ DateTime.Now.ToString("yyyyMMddHHmmssfff")
           });

            return new {
                add= add,
                list = dal.ExecuteDataTable("select * from test3").ToPagingData(10,1)
            };
        }

        [HttpGet]
        public dynamic update()
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);
            
            bool update = dal.Update("test3", new
            {
                //a = 88,
                c = DateTime.Now,
                d = "update" + DateTime.Now.ToString("yyyyMMddHHmmssfff")
            }, " where id=1 ");

            dal.Update("test3", new
            {
                //a = 88,
                c = DateTime.Now,
                d = "WhereParameter update " + DateTime.Now.ToString("yyyyMMddHHmmssfff")
            },new WhereParameter("where id=@id",new { id =2 }));

            return new
            {
                update = update,
                list = dal.ExecuteDataTable("select * from test3 where id in (1,2,2)   ")
            };
        }

        [HttpGet]
        public dynamic tran()
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);
            DataTable preList = dal.ExecuteDataTable("select * from test3 where id in (1,2)   ");

            dal.ExecuteTransaction(tran => {

                DateTime cc = DateTime.Now;
                bool update = tran.Update("test3", new
                {
                    //a = 88,
                    c = cc
                }, " where id=1 ");

                try
                {
                    int a = 1;
                    int b = 10;

                    int ccc = a / b;

                    tran.Update("test3", new
                    {
                        c = cc
                    }, new WhereParameter("where id=@id", new { id = 2 }));
                }
                catch (Exception ex)
                {
                    string msg = ex.Message;
                    throw ex;
                }

            });


            return new
            {
                preList = preList,
                list = dal.ExecuteDataTable("select * from test3 where id in (1,2)   ")
            };
        }


        [HttpGet]
        public dynamic t1()
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);
            bool add = dal.Add("test3", new
            {
                a = DotNetDAL.Snowflake.SnowflakeIdWorker.CreateId(),
                //b8 = "13",
                //bb=33,
                c = DateTime.Now,
                d = DateTime.Now.ToString("yyyyMMddHHmmssfff")
            });

            bool update = dal.Update("test3", new
            {
                //a = 88,
                c = DateTime.Now,
                d = "update2222"
            }, " where id=1 ");


            return new
            {
                add = add,
                update = update,
                list = dal.ExecuteDataTable("select * from test3")
            };
        }


        [HttpGet]
        public dynamic t2()
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);

            SqlParams par1 = new SqlParams();
            par1.Sql = "insert into test2(a,b) values(@a,@b)";
            par1.Parameter = new {
                a=2,
                b=3
            };

            SqlParams par2 = new SqlParams();
            par2.Sql = "insert into test2(a,b) values(@a,@b)";
            par2.Parameter = new
            {
                a = 3,
                b = 4
            };

            List<SqlParams> pList = new List<SqlParams>() {
                par1,par2
            };

            dal.ExecuteNonQuery(pList);
            dal.ExecuteNonQuery("insert into test2(a,b) values(@a,@b)", new {
                a = 4,
                b = 5
            });





            return new
            {
                list = dal.ExecuteDataTable("select * from test2")
            };
        }


        [HttpGet]
        public dynamic t3()
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);

            return new
            {
                DataTable = dal.ExecuteDataTable("select * from test2 where a=@a",new { a=2}),
                ExecuteDataSet = dal.ExecuteDataSet("select * from test2 where a=@a", new { a = 2 }),
                ExecuteReader = dal.ExecuteReader("select * from test2 where a=@a", new { a = 2 }),
                ExecutePage = dal.ExecutePage(10,1,"id", "select * ", "from test2 where a=@a", new { a = 2 }),
                ExecuteScalar = dal.ExecuteScalar("select * from test2 where a=@a", new { a = 2 }),
                ExecuteScalarString = dal.ExecuteScalarString("select * from test2 where a=@a", new { a = 2 })

            };
        }




        [HttpGet]
        public dynamic t4(int i=0)
        {
            SqlServerConnector dal = new SqlServerConnector(connectionString);


            int procReturn=-100;
            Dictionary<string, object> procOutputs=null;
            object Data = null;

            if (i == 2)
            {
                Data = dal.ExecuteProcDataTable("testPrco2", out procReturn, out procOutputs, new { p1 = "p1p1p1" });
            }
            else if (i == 3)
            {
                Data = dal.ExecuteProcDataSet("testPrco3", out procReturn, out procOutputs, new { p1 = "p1p1p1" });
            }
            else {
                Data = dal.ExecuteProc("testPrco", out procReturn, out procOutputs, new { });
            }



            return new
            {
                procReturn = procReturn,
                procOutputs = procOutputs,
                Data = Data

            };
        }


















    }

}














public class TestData: ICopyEntity
    {
        public string id { get; set; }
        public int age;
        public DateTime? createtime { get; set; }
    }


    public class TestData2
    {
        public string id;
        public string name { get; set; }
        public int age;
        public int sum { get; set; }
        public DateTime? createtime { get; set; }
    }

    public class TestData3
    {
        public int id { get; set; }
        public int a { get; set; }
        public int b { get; set; }
        public DateTime? c { get; set; }
        public string d { get; set; }
    }

